﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GenUtil.Util;
using System.Data.Common;
using GenUtil.Dto;

namespace DataLayer.Dao.Sql
{
    public class CustomerDaoSql : BasicDaoSql, CustomerDao
    {
        private static Logger logger = new Logger(typeof(CustomerDaoSql));

        private static string SQL_INSERT_CUSTOMER = @" INSERT INTO " + SCHEMA + @".CUSTOMER (CUSTOMER_NO,FIRST_NAME,LAST_NAME,EMAIL_ID,
        PHONE_NO,ADDRESS_1,ADDRESS_2,CITY,STATE,LAST_UPD_DT,LAST_UPD_USERID) 
        VALUES (@CUSTOMER_NO,@FIRST_NAME,@LAST_NAME,@EMAIL_ID,@PHONE_NO,@ADDRESS_1,@ADDRESS_2,@CITY,@STATE,@LAST_UPD_DT,@LAST_UPD_USERID) ";
        public void InsertCustomerDto(CustomerDto customerDto)
        {
            logger.debug("Entering CustomerDaoSql.InsertCustomerDto()");
            DbConnection con = null;
            DbCommand com = null;
            try
            {
                con = getDatabaseConnection();
                com = CreateDBCommand(con, SQL_INSERT_CUSTOMER);
                setStatement(null, com, customerDto);
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                logger.fatal("Exception in CustomerDaoSql.InsertCustomerDto():" + e.Message, e);
                throw new DaoException("Exception in CustomerDaoSql.InsertCustomerDto(): " + e.Message, e);
            }
            finally
            {
                if (com != null) com.Cancel();
                if (con != null) con.Close();
            }
            logger.debug("Exiting CustomerDaoSql.InsertCustomerDto()");
        }
        private void setStatement(String methodName, DbCommand com, CustomerDto customerDto)
        {

            DbParameter[] chkPara = CreateDBParameter(com, 11);

            if (methodName == null)
            {
                PopulateDBParameter(chkPara[0], "CUSTOMER_NO", customerDto.CustomerNo);
                PopulateDBParameter(chkPara[1], "FIRST_NAME", customerDto.FirstName);
                PopulateDBParameter(chkPara[2], "LAST_NAME", customerDto.LastName);
                PopulateDBParameter(chkPara[3], "EMAIL_ID", customerDto.EmailId);
                PopulateDBParameter(chkPara[4], "PHONE_NO", customerDto.PhoneNo);
                PopulateDBParameter(chkPara[5], "ADDRESS_1", customerDto.Address1);
                PopulateDBParameter(chkPara[6], "ADDRESS_2", customerDto.Address2);
                PopulateDBParameter(chkPara[7], "CITY", customerDto.City);
                PopulateDBParameter(chkPara[8], "STATE", customerDto.State);
                PopulateDBParameter(chkPara[9], "LAST_UPD_DT", customerDto.LastUpdDt);
                PopulateDBParameter(chkPara[10], "LAST_UPD_USERID", customerDto.LastUpdUserId);

                com.Parameters.AddRange(chkPara);

            }
        }
        private static string SQL_GET_CUSTOMER = @"SELECT * FROM CUSTOMER where CUSTOMER_NO = @CUSTOMER_NO";

        public CustomerDto GetCustomerDto(int customerNo)
        {
            logger.debug("Entering CustomerDaoSql.getCustomerDto()");

            CustomerDto customerDto = null;

            DbConnection con = null;
            DbCommand pstmt = null;
            DbDataReader rs = null;

            Dictionary<string, object> stringofpara = new Dictionary<string, object>();
            stringofpara.Add("customerNo", customerNo);

            try
            {
                con = getDatabaseConnection();
                pstmt = CreateDBCommand(con, SQL_GET_CUSTOMER);
                DbParameter[] para = CreateDBParameter(pstmt, 1);
                PopulateDBParameter(para[0], "CUSTOMER_NO", customerNo);
                pstmt.Parameters.AddRange(para);
                rs = pstmt.ExecuteReader();

                if (rs.Read())
                {
                    customerDto = rsToDto(rs);
                }
            }
            catch (Exception e)
            {
                logger.fatal("Exception occured in CustomerDaoSql.getCustomerDto():" + e.Message, e);
                throw new Exception("Exception : CustomerDaoSql.getCustomerDto() : " + e.Message, e);
            }
            finally
            {
                if (rs != null) rs.Close();
                if (pstmt != null) pstmt.Dispose();
                if (con != null) con.Close();
            }
            logger.debug("exiting CustomerDaoSql.getCustomerDto()");
            return customerDto;
        }

        private static string SQL_GET_MAX_CUSTOMER_NO = @"SELECT Max(CUSTOMER_NO) FROM CUSTOMER";

        public int getMaxCustomerNo()
        {
            logger.debug("Entering CustomerDaoSql.getMaxCustomerNo()");

            int maxCustomerNo = 0;

            DbConnection con = null;
            DbCommand pstmt = null;
            DbDataReader rs = null;

            try
            {
                con = getDatabaseConnection();
                pstmt = CreateDBCommand(con, SQL_GET_MAX_CUSTOMER_NO);
                rs = pstmt.ExecuteReader();
                if (rs.Read())
                {
                    maxCustomerNo = rs[0] == DBNull.Value ? 0 : Convert.ToInt32(rs[0]);
                }
            }
            catch (Exception e)
            {
                logger.fatal("Exception occured in CustomerDaoSql.getMaxCustomerNo():" + e.Message, e);
                throw new Exception("Exception : CustomerDaoSql.getMaxCustomerNo() : " + e.Message, e);
            }
            finally
            {
                if (rs != null) rs.Close();
                if (pstmt != null) pstmt.Dispose();
                if (con != null) con.Close();
            }
            logger.debug("exiting CustomerDaoSql.getMaxCustomerNo()");
            return maxCustomerNo;
        }
        private CustomerDto rsToDto(DbDataReader rs)
        {
            CustomerDto odCustomerDto = null;

            if (rs != null)
            {
                odCustomerDto = new CustomerDto();

                odCustomerDto.setCustomerNo(getIntValue(rs, "CUSTOMER_NO"));
                odCustomerDto.setFirstName(getStringValue(rs, "FIRST_NAME"));
                odCustomerDto.setLastName(getStringValue(rs, "LAST_NAME"));
                odCustomerDto.setEmailId(getStringValue(rs, "EMAIL_ID"));
                odCustomerDto.setPhoneNo(getStringValue(rs, "PHONE_NO"));
                odCustomerDto.setAddress1(getStringValue(rs, "ADDRESS_1"));
                odCustomerDto.setAddress2(getStringValue(rs, "ADDRESS_2"));
                odCustomerDto.setCity(getStringValue(rs, "CITY"));
                odCustomerDto.setState(getStringValue(rs, "STATE"));
                odCustomerDto.setLastUpdDt(getDateTimeValue(rs, "LAST_UPD_DT"));
                odCustomerDto.setLastUpdUserId(getStringValue(rs, "LAST_UPD_USERID"));
            }
            return odCustomerDto;
        }

        private static string SQL_SELECT_CUSTOMER = @"SELECT * FROM CUSTOMER ";

        public List<CustomerDto> getCustomerDtos()
        {
            logger.debug("Entering CustomerDaoSql.getCustomerDto()");

            List<CustomerDto> customerDtos = new List<CustomerDto>();

            DbConnection con = null;
            DbCommand pstmt = null;
            DbDataReader rs = null;

            CustomerDto customerId = null;

            try
            {
                con = getDatabaseConnection();
                pstmt = CreateDBCommand(con, SQL_SELECT_CUSTOMER);
                rs = pstmt.ExecuteReader();

                while (rs.Read())
                {
                    customerId = rsToDto(rs);
                    customerDtos.Add(customerId);
                }
            }
            catch (Exception e)
            {
                logger.fatal("Exception occured in CustomerDaoSql.getMaxCustomerNo():" + e.Message, e);
                throw new Exception("Exception : CustomerDaoSql.getMaxCustomerNo() : " + e.Message, e);
            }
            finally
            {
                if (pstmt != null) pstmt.Cancel();
                if (con != null) con.Close();
            }
            logger.debug("Exiting CustomerDaoSql.getMaxCustomerNo()");
            return customerDtos;
        }
    }
}


